﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using MyATM.Entity;

namespace MyATM.DataAccess
{
    /// <summary>
    /// This class provide methods to access the Card class.
    /// </summary>
    public class CardDA
    {
        /// <summary>
        /// Xac thuc the nhap vao
        /// </summary>
        /// <param name="cardId">ID cua card</param>
        /// <param name="cardPin">Ma PIN</param>
        /// <returns></returns>
        public bool AuthenticateCard(string cardId, string cardPin)
        {
            string cmd =
                string.Format(
                    "SELECT 1 FROM DebitCard WHERE DebitCard.CardNumber = \"{0}\" AND DebitCard.CardPIN = \"{1}\"",
                    cardId, cardPin);
            int tempResult = SqlAccess.ExcuteQuery(cmd).Rows.Count;
            if (tempResult == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Kiem tra the nhap vao co hop le.
        /// </summary>
        /// <param name="cardId">ID cua Card</param>
        /// <returns></returns>
        public bool ValidateCard(string cardId)
        {
            string cmd = string.Format("SELECT 1 FROM DebitCard WHERE (((DebitCard.[CardNumber])=\"{0}\"));", cardId);
            int tempResult = SqlAccess.ExcuteQuery(cmd).Rows.Count;
            if (tempResult == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Xem trang thai cua the
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public int GetStatus(string cardId)
        {
            string cmd = string.Format("SELECT DebitCard.Status FROM DebitCard WHERE (((DebitCard.[CardNumber])=\"{0}\"));",cardId);
            int tempResult = Convert.ToInt32(SqlAccess.ExcuteQuery(cmd).Rows[0][0].ToString());
            return tempResult;
        }
        /// <summary>
        /// Cap nhat ma PIN cho the
        /// </summary>
        /// <param name="cardID"></param>
        /// <param name="cardPin"></param>
        /// <returns></returns>
        public int UpdatePinCard(string cardID, string cardPin)
        {
            try
            {
                string cmd =
                    string.Format(
                        "UPDATE DebitCard SET DebitCard.CardPIN = \"{0}\" WHERE (((DebitCard.CardNumber)=\"{1}\"));",
                        cardPin, cardID);
                int result = SqlAccess.ExcuteNonQuery(cmd);
                return result;
            }
            catch (Exception)
            {
                throw new Exception("Error in update PIN");
            }
            
        }
        /// <summary>
        /// Lay ve luong tien da rut trong 1 ngay
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public  double GetAmountInDay(string cardId)
        {
            string cmd =
                string.Format(
                    "SELECT Sum(Transaction.Amount) AS SumOfAmount FROM DebitCard INNER JOIN [Transaction] ON " +
                    "DebitCard.CardNumber = Transaction.CardNumber WHERE (((Transaction.[TransTime])=Date()) AND " +
                    "((DebitCard.[CardNumber])=\"{0}\")) GROUP BY DebitCard.CardNumber",cardId);
            DataTable dataTable = new DataTable();
            dataTable = SqlAccess.ExcuteQuery(cmd);
            if (dataTable.Rows.Count != 0)
            {
                return Convert.ToDouble(dataTable.Rows[0][0].ToString());
            }
            else
            {
                return 0;
            }
        }
        public bool BlockCard()
        {
            return true;
        }
    }
}
